﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
using System.Data.OleDb;
using CommClass;

namespace DMS
{
    public partial class FrmImportsurplierprod : DMS.FrmTemplate
    {
        DataConnection dc = new DataConnection();
        DataSource dsExcelFile;
        Surplier sp = new Surplier(App.Ds);  
        public FrmImportsurplierprod()     
        {
            InitializeComponent();
        }

        private void FrmDoImportSAP_Load(object sender, EventArgs e)
        {

        }
       
      



        private void button1_Click_1(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Filter = "Excel文件|*.xls;*.xlsx|所有文件|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.tbFileName.Text = dlg.FileName;
                dlg.Dispose();
                dc.DBName = tbFileName.Text;
                dsExcelFile = new ExcelDataSource(dc);
                cbbSheets.DataSource = dsExcelFile.GetTableNames();
                cbbSheets.SelectedIndex = 0;
            }


        }

        private void button2_Click_1(object sender, EventArgs e)
        {
            if (tbFileName.Text != null && tbFileName.Text.Trim() != "")
            {
                string constr = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';Data Source=" + tbFileName.Text;
                OleDbConnection myOleDbConnection = new OleDbConnection(constr);
                OleDbDataAdapter myOleDbDataAdapter = new OleDbDataAdapter("select * from [" + cbbSheets.Text + "] order by 供应商名称 ", myOleDbConnection);
                DataTable myDataTable = new DataTable();
                myOleDbDataAdapter.Fill(myDataTable);



                //显示excel数据
                dvgDO.DataSource = myDataTable;
                //dsExcelFile.GetRecord("select * from [" + cbbSheets.Text + "] order by 提单号 ").Tables[0];

                this.button3.Enabled = true;
            }
            else
            {
                  MessageBox .Show ( "请选择文件！");
            }
        }

        private void button3_Click_1(object sender, EventArgs e)
        {
            tabControl1.SelectedIndex = 1;
            listBox1.Items.Add("开始导入数据...");
            ////获取excel数据
            DataTable dtt =(DataTable)dvgDO.DataSource;


            //遍历excel数据并上传至数据库


            //Math.Round(45.367,2)  
            using (SqlConnection conn = new SqlConnection(App.GetSqlConnection()))
            {
                conn.Open();
                using (SqlTransaction tran = conn.BeginTransaction()) //开始数据库事务。即创建一个事务对象tran  
                {
                    using (SqlCommand cmd = new SqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.Transaction = tran; //获取或设置将要其执行的事务  
                        try
                        {
                            if (dtt.Rows.Count > 0)
                            {
                                for (int i = 0; i < dtt.Rows.Count; i++)
                                {


                                    SupplySupplierProduct ssp = new SupplySupplierProduct();
                                    ssp.SupplySupplierID  = sp.GetSupplySupplierID(dtt.Rows[i]["供应商名称"].ToString().Trim(), conn, cmd);
                                    ssp.ProdID = sp.GetProdID(dtt.Rows[i]["产品代码"].ToString().Trim(), conn, cmd);
                                    ssp.ShangpinshuxingID = sp.GetshangpinshuxingID(dtt.Rows[i]["性质"].ToString().Trim(), conn, cmd);
                                    ssp.Youxianji = int.Parse(dtt.Rows[i]["供应商优先级"].ToString().Trim());
                                    ssp.Caigoujia = double.Parse(dtt.Rows[i]["采购价"].ToString().Trim());
                                    double wuliufei = 0;
                                    if (dtt.Rows[i]["物流费"].ToString() != null && dtt.Rows[i]["物流费"].ToString().Trim() != "")
                                    {
                                        double.TryParse(dtt.Rows[i]["物流费"].ToString().Trim(), out wuliufei);
                                    }
                                    ssp.Wuliufei = wuliufei;

                                    double qitafeiyong = 0;
                                    if (dtt.Rows[i]["其他费用"].ToString() != null && dtt.Rows[i]["其他费用"].ToString().Trim() != "")
                                    {
                                        double.TryParse(dtt.Rows[i]["其他费用"].ToString().Trim(), out qitafeiyong);
                                    }
                                    ssp.Qitafeiyong = qitafeiyong;
                                    ssp.FapiaoID = sp.GetfapiaoID(dtt.Rows[i]["发票"].ToString().Trim(), conn, cmd);
                                    ssp.Fandian = dtt.Rows[i]["返点力度"].ToString().Trim();
                                    ssp.SupplySupplierProductID = sp.GetSupplySupplierProductID(Convert.ToString(ssp.SupplySupplierID), Convert.ToString(ssp.ProdID), conn, cmd);

                                    if (ssp.SupplySupplierProductID>0)
                                    {
                                       
                                        ssp.updateSupplySupplierProduct(ssp, conn, cmd);
                                    }
                                    else
                                    {
                                        ssp.SupplySupplierProductID = ssp.saveSupplySupplierProduct(ssp, conn, cmd);
                                    }

                                }
                            }
                            tran.Commit();
                            conn.Close();
                            MessageBox.Show("上传成功");
                       }
                        catch
                        {

                            tran.Rollback();//如果执行不成功，发送异常，则执行rollback方法，回滚到事务操作开始之前。
                           conn.Close();
                            MessageBox.Show("上传失败");
                        }
                    }
                }

            }
            listBox1.Items.Add("数据导入完成！");

         }

        private void button4_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void cbbSheets_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void label2_Click(object sender, EventArgs e)
        {

        }

        private void label1_Click(object sender, EventArgs e)
        {

        }

        private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void tbFileName_TextChanged(object sender, EventArgs e)
        {

        }
    }
    
}

